package server;

import java.io.PrintWriter;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Sasa
 */
public class DataBase {
    private Connection con;

    public DataBase() throws Exception {
        connect("localhost:3306", "collab", "root", "");
    }

    public void connect(String host, String name, String user, String pass) throws Exception {
       Class.forName("com.mysql.jdbc.Driver");
       this.con = DriverManager.getConnection("jdbc:mysql://"+host+"/"+name, user, pass);
       System.out.println("Connected to database: "+ host);
    }


    public Integer checkLogin(String username, String password) throws Exception {
        PreparedStatement ps = this.con.prepareStatement("SELECT * FROM members WHERE Username = ? AND Password = ? LIMIT 1");

        ps.setString(1, username);
        ps.setString(2, password);

        ResultSet rs = ps.executeQuery();

        if(rs.next()) {
            return rs.getInt("Id");
        }
        return 0;
    }

    public Boolean completeTask(Integer taskId) throws Exception {
        PreparedStatement ps = this.con.prepareStatement("UPDATE tasks SET Completed = 1 WHERE Id = ?");

        ps.setInt(1, taskId);

        ps.executeUpdate();
        return true;
    }

    public Boolean addTask(Integer memberId, String assigment) throws Exception {
        PreparedStatement ps = this.con.prepareStatement("INSERT tasks (MemberId, Assigment, Completed) VALUES(?, ?, 0)");

        ps.setInt(1, memberId);
        ps.setString(2, assigment);
        ps.executeUpdate();
        return true;
    }

    public Boolean addMember(String username, String password) throws Exception {
	PreparedStatement ps = this.con.prepareStatement("INSERT members (Username, Password) VALUES(?, ?)");

        ps.setString(1, username);
        ps.setString(2, password);
        ps.executeUpdate();
        return true;
    }

    public String xmlTasks(Integer userId) throws Exception {
        String filename = "xmls/tasks-"+userId+".xml";

        PreparedStatement ps = this.con.prepareStatement("SELECT * FROM tasks WHERE MemberId = ? AND Completed = 0");
        ps.setInt(1, userId);

        ResultSet rs = ps.executeQuery();

        PrintWriter pw = new PrintWriter(filename);
        myXML xmlRoot = new myXML("tasks");

        while(rs.next()) {
           myXML xmlTask = xmlRoot.addElement("task");
           xmlTask.addElement("id", rs.getInt("Id"));
           xmlTask.addElement("memberId", rs.getInt("MemberId"));
           xmlTask.addElement("assigment", rs.getString("Assigment"));
        }
        xmlRoot.serialize(pw);
        pw.close();

        return filename;
    }

    public String xmlMembers() throws Exception {
        
        String filename = "xmls/members.xml";

        Statement st = this.con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM members");

        PrintWriter pw = new PrintWriter(filename);
        myXML xmlRoot = new myXML("members");

        while(rs.next()) {
            myXML xmlMember = xmlRoot.addElement("member");
            xmlMember.addElement("id", rs.getInt("Id"));
            xmlMember.addElement("username", rs.getString("Username"));
        }
        
        xmlRoot.serialize(pw);
        pw.close();
        
        return filename;
    }
}
